#include "upload_database.h"

void UploadDatabase::initUploadTable()
{
    QSqlQuery query;

    // 创建上传记录表
    QString createTableSql =
        "CREATE TABLE IF NOT EXISTS upload_records ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "device_id TEXT NOT NULL, "               // 设备ID
        "server_ip TEXT NOT NULL, "               // 服务器IP
        "server_port INTEGER NOT NULL, "          // 服务器端口
        "data_content TEXT, "                     // 上传数据内容
        "data_size INTEGER NOT NULL DEFAULT 0, "  // 数据大小
        "status TEXT NOT NULL, "                  // 状态：成功/失败
        "error_message TEXT, "                    // 错误信息
        "upload_time TEXT NOT NULL, "             // 上传时间
        "response_time INTEGER DEFAULT 0 "        // 响应时间（毫秒）
        ")";

    // 创建索引
    QString createIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_upload_device_time "
        "ON upload_records (device_id, upload_time DESC)";

    QString createStatusIndexSql =
        "CREATE INDEX IF NOT EXISTS idx_upload_status "
        "ON upload_records (status)";

    if (!query.exec(createTableSql) || !query.exec(createIndexSql) ||
        !query.exec(createStatusIndexSql)) {
        qWarning() << "上传记录表初始化失败：" << query.lastError().text();
    } else {
        qDebug() << "上传记录表初始化成功";
    }
}

bool UploadDatabase::addUploadRecord(const UploadRecord &record)
{
    QSqlDatabase db = getThreadSafeDatabase();
    if (!db.isOpen()) {
        qWarning() << "数据库连接未打开";
        return false;
    }

    QSqlQuery query(db);
    query.prepare(
        "INSERT INTO upload_records (device_id, server_ip, server_port, data_content, "
        "data_size, status, error_message, upload_time, response_time) "
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

    query.bindValue(0, record.deviceId);
    query.bindValue(1, record.serverIP);
    query.bindValue(2, record.serverPort);
    query.bindValue(3, record.dataContent);
    query.bindValue(4, record.dataSize);
    query.bindValue(5, record.status);
    query.bindValue(6, record.errorMessage);
    query.bindValue(7, record.uploadTime.toString("yyyy-MM-dd hh:mm:ss"));
    query.bindValue(8, record.responseTime);

    if (!query.exec()) {
        qWarning() << "添加上传记录失败：" << query.lastError().text();
        return false;
    }

    return true;
}

int UploadDatabase::addUploadRecordAndGetId(const UploadRecord &record)
{
    QSqlDatabase db = getThreadSafeDatabase();
    if (!db.isOpen()) {
        qWarning() << "数据库连接未打开";
        return -1;
    }

    QSqlQuery query(db);
    query.prepare(
        "INSERT INTO upload_records (device_id, server_ip, server_port, data_content, "
        "data_size, status, error_message, upload_time, response_time) "
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

    query.bindValue(0, record.deviceId);
    query.bindValue(1, record.serverIP);
    query.bindValue(2, record.serverPort);
    query.bindValue(3, record.dataContent);
    query.bindValue(4, record.dataSize);
    query.bindValue(5, record.status);
    query.bindValue(6, record.errorMessage);
    query.bindValue(7, record.uploadTime.toString("yyyy-MM-dd hh:mm:ss"));
    query.bindValue(8, record.responseTime);

    if (!query.exec()) {
        qWarning() << "添加上传记录失败：" << query.lastError().text();
        return -1;
    }

    // 获取最后插入的记录ID
    return query.lastInsertId().toInt();
}

bool UploadDatabase::updateUploadStatus(int recordId, const QString &status, const QString &error)
{
    QSqlDatabase db = getThreadSafeDatabase();
    if (!db.isOpen()) {
        qWarning() << "数据库连接未打开";
        return false;
    }

    QSqlQuery query(db);
    query.prepare("UPDATE upload_records SET status = ?, error_message = ? WHERE id = ?");
    query.bindValue(0, status);
    query.bindValue(1, error);
    query.bindValue(2, recordId);

    if (!query.exec()) {
        qWarning() << "更新上传状态失败：" << query.lastError().text();
        return false;
    }

    return query.numRowsAffected() > 0;
}

bool UploadDatabase::updateResponseTime(int recordId, qint64 responseTime)
{
    QSqlQuery query;
    query.prepare("UPDATE upload_records SET response_time = ? WHERE id = ?");
    query.bindValue(0, responseTime);
    query.bindValue(1, recordId);

    return query.exec() && query.numRowsAffected() > 0;
}

QVector<UploadRecord> UploadDatabase::getUploadRecords(int limit)
{
    QVector<UploadRecord> records;
    QSqlQuery query;

    query.prepare("SELECT * FROM upload_records ORDER BY upload_time DESC LIMIT ?");
    query.bindValue(0, limit);

    if (query.exec()) {
        while (query.next()) {
            records.append(queryToUploadRecord(query));
        }
    } else {
        qWarning() << "获取上传记录失败：" << query.lastError().text();
    }

    return records;
}

QVector<UploadRecord> UploadDatabase::getUploadRecordsByDevice(const QString &deviceId, int limit)
{
    QVector<UploadRecord> records;
    QSqlQuery query;

    query.prepare(
        "SELECT * FROM upload_records WHERE device_id = ? ORDER BY upload_time DESC LIMIT ?");
    query.bindValue(0, deviceId);
    query.bindValue(1, limit);

    if (query.exec()) {
        while (query.next()) {
            records.append(queryToUploadRecord(query));
        }
    } else {
        qWarning() << "获取设备上传记录失败：" << query.lastError().text();
    }

    return records;
}

QVector<UploadRecord> UploadDatabase::getUploadRecordsByDateRange(const QDateTime &startTime,
                                                                  const QDateTime &endTime)
{
    QVector<UploadRecord> records;
    QSqlQuery query;

    query.prepare(
        "SELECT * FROM upload_records WHERE upload_time BETWEEN ? AND ? ORDER BY upload_time DESC");
    query.bindValue(0, startTime.toString("yyyy-MM-dd hh:mm:ss"));
    query.bindValue(1, endTime.toString("yyyy-MM-dd hh:mm:ss"));

    if (query.exec()) {
        while (query.next()) {
            records.append(queryToUploadRecord(query));
        }
    } else {
        qWarning() << "按时间范围获取上传记录失败：" << query.lastError().text();
    }

    return records;
}

UploadRecord UploadDatabase::getUploadRecordById(int id)
{
    UploadRecord record;
    QSqlQuery query;

    query.prepare("SELECT * FROM upload_records WHERE id = ?");
    query.bindValue(0, id);

    if (query.exec() && query.next()) {
        record = queryToUploadRecord(query);
    }

    return record;
}

int UploadDatabase::getTotalUploadCount()
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM upload_records");

    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }

    return 0;
}

int UploadDatabase::getSuccessUploadCount()
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM upload_records WHERE status = '成功'");

    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }

    return 0;
}

int UploadDatabase::getFailedUploadCount()
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM upload_records WHERE status = '失败'");

    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }

    return 0;
}

int UploadDatabase::getUploadCountByDevice(const QString &deviceId)
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM upload_records WHERE device_id = ?");
    query.bindValue(0, deviceId);

    if (query.exec() && query.next()) {
        return query.value(0).toInt();
    }

    return 0;
}

double UploadDatabase::getSuccessRate()
{
    int total = getTotalUploadCount();
    if (total == 0) return 0.0;

    int success = getSuccessUploadCount();
    return (double)success / total * 100.0;
}

double UploadDatabase::getSuccessRateByDevice(const QString &deviceId)
{
    QSqlQuery query;

    // 获取设备总数
    query.prepare("SELECT COUNT(*) FROM upload_records WHERE device_id = ?");
    query.bindValue(0, deviceId);

    if (!query.exec() || !query.next()) return 0.0;
    int total = query.value(0).toInt();
    if (total == 0) return 0.0;

    // 获取成功数
    query.prepare("SELECT COUNT(*) FROM upload_records WHERE device_id = ? AND status = '成功'");
    query.bindValue(0, deviceId);

    if (!query.exec() || !query.next()) return 0.0;
    int success = query.value(0).toInt();

    return (double)success / total * 100.0;
}

bool UploadDatabase::deleteOldRecords(int daysToKeep)
{
    QDateTime cutoffTime = QDateTime::currentDateTime().addDays(-daysToKeep);

    QSqlQuery query;
    query.prepare("DELETE FROM upload_records WHERE upload_time < ?");
    query.bindValue(0, cutoffTime.toString("yyyy-MM-dd hh:mm:ss"));

    if (!query.exec()) {
        qWarning() << "删除旧记录失败：" << query.lastError().text();
        return false;
    }

    qDebug() << "已删除" << query.numRowsAffected() << "条旧记录";
    return true;
}

bool UploadDatabase::clearAllRecords()
{
    QSqlQuery query;
    query.prepare("DELETE FROM upload_records");

    if (!query.exec()) {
        qWarning() << "清空上传记录失败：" << query.lastError().text();
        return false;
    }

    return true;
}

UploadRecord UploadDatabase::getLastUploadRecord(const QString &deviceId)
{
    UploadRecord record;
    QSqlQuery query;

    query.prepare(
        "SELECT * FROM upload_records WHERE device_id = ? ORDER BY upload_time DESC LIMIT 1");
    query.bindValue(0, deviceId);

    if (query.exec() && query.next()) {
        record = queryToUploadRecord(query);
    }

    return record;
}

QVector<UploadRecord> UploadDatabase::getRecentFailedRecords(int count)
{
    QVector<UploadRecord> records;
    QSqlQuery query;

    query.prepare(
        "SELECT * FROM upload_records WHERE status = '失败' ORDER BY upload_time DESC LIMIT ?");
    query.bindValue(0, count);

    if (query.exec()) {
        while (query.next()) {
            records.append(queryToUploadRecord(query));
        }
    } else {
        qWarning() << "获取失败记录失败：" << query.lastError().text();
    }

    return records;
}

UploadRecord UploadDatabase::queryToUploadRecord(const QSqlQuery &query)
{
    UploadRecord record;
    record.id = query.value("id").toInt();
    record.deviceId = query.value("device_id").toString();
    record.serverIP = query.value("server_ip").toString();
    record.serverPort = query.value("server_port").toUInt();
    record.dataContent = query.value("data_content").toString();
    record.dataSize = query.value("data_size").toLongLong();
    record.status = query.value("status").toString();
    record.errorMessage = query.value("error_message").toString();
    record.uploadTime =
        QDateTime::fromString(query.value("upload_time").toString(), "yyyy-MM-dd hh:mm:ss");
    record.responseTime = query.value("response_time").toLongLong();

    return record;
}

int UploadDatabase::getTotalUploadRecords(const QDate &date)
{
    QSqlQuery query;
    int count = 0;

    QString sql = "SELECT COUNT(*) FROM upload_records WHERE 1=1";
    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        sql += " AND date(upload_time) = '" + dateStr + "'";
    }

    if (query.exec(sql) && query.next()) {
        count = query.value(0).toInt();
    } else {
        qWarning() << "获取上传记录总数失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return count;
}

QVector<QVariantMap> UploadDatabase::queryUploadRecordsByPage(int page, int pageSize,
                                                              const QDate &date)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    // 计算偏移量
    int offset = (page - 1) * pageSize;

    QString sql = "SELECT * FROM upload_records WHERE 1=1";

    // 添加日期过滤条件
    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        sql += " AND date(upload_time) = '" + dateStr + "'";
    }

    sql += " ORDER BY upload_time DESC LIMIT " + QString::number(pageSize) + " OFFSET " +
           QString::number(offset);

    if (query.exec(sql)) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_id"] = query.value("device_id");
            record["server_ip"] = query.value("server_ip");
            record["server_port"] = query.value("server_port");
            record["data_content"] = query.value("data_content");
            record["data_size"] = query.value("data_size");
            record["status"] = query.value("status");
            record["error_message"] = query.value("error_message");
            record["upload_time"] = query.value("upload_time");
            record["response_time"] = query.value("response_time");

            // 添加格式化的状态文本
            QString status = query.value("status").toString();
            record["status_text"] = (status == "成功") ? "✅ 成功" : "❌ 失败";

            results.append(record);
        }
    } else {
        qWarning() << "分页查询上传记录失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return results;
}

QVector<QVariantMap> UploadDatabase::queryUploadRecordsByDate(const QDate &date)
{
    QVector<QVariantMap> results;
    QSqlQuery query;

    QString sql = "SELECT * FROM upload_records WHERE 1=1";

    // 添加日期过滤条件
    if (date.isValid()) {
        QString dateStr = date.toString("yyyy-MM-dd");
        sql += " AND date(upload_time) = '" + dateStr + "'";
    }

    sql += " ORDER BY upload_time DESC";

    if (query.exec(sql)) {
        while (query.next()) {
            QVariantMap record;
            record["id"] = query.value("id");
            record["device_id"] = query.value("device_id");
            record["server_ip"] = query.value("server_ip");
            record["server_port"] = query.value("server_port");
            record["data_content"] = query.value("data_content");
            record["data_size"] = query.value("data_size");
            record["status"] = query.value("status");
            record["error_message"] = query.value("error_message");
            record["upload_time"] = query.value("upload_time");
            record["response_time"] = query.value("response_time");

            // 添加格式化的状态文本
            QString status = query.value("status").toString();
            record["status_text"] = (status == "成功") ? "✅ 成功" : "❌ 失败";

            results.append(record);
        }
    } else {
        qWarning() << "按日期查询上传记录失败：" << query.lastError().text() << "SQL:" << sql;
    }

    return results;
}

QSqlDatabase UploadDatabase::getThreadSafeDatabase()
{
    // 为每个线程创建唯一的连接名
    QString connectionName =
        QString("upload_thread_%1").arg(reinterpret_cast<quintptr>(QThread::currentThread()));

    QSqlDatabase db;
    if (!QSqlDatabase::contains(connectionName)) {
        // 为当前线程创建新的数据库连接
        db = QSqlDatabase::addDatabase("QSQLITE", connectionName);
        db.setDatabaseName("database.db");

        if (!db.open()) {
            qWarning() << "无法为线程创建数据库连接：" << db.lastError().text();
        }
    } else {
        db = QSqlDatabase::database(connectionName);
    }

    return db;
}
